package com.example.utils;

import jxl.Sheet;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import org.apache.commons.io.FileUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;

/**
 * Created by feilin on 2017/5/9.
 * Excel文件导入导出
 */
public class ExcelUtil {

    public static void jxlExport() {
        String[] titles = {"id", "name", "sex"};
        File excel = new File("./jxl_excel.xls");
        try {
            excel.createNewFile();

            //创建工作簿
            WritableWorkbook workbook = Workbook.createWorkbook(excel);
            WritableSheet sheet = workbook.createSheet("sheet1", 0);
            Label label = null;
            // 表头
            for (int i=0; i<titles.length; i++) {
                label = new Label(i, 0, titles[i]);
                sheet.addCell(label);
            }

            // 追加数据
            for (int i=1; i<10; i++) {
                label = new Label(0, i, "a"+i);
                sheet.addCell(label);
                label = new Label(1, i, "user"+i);
                sheet.addCell(label);
                label = new Label(2, i, "male");
                sheet.addCell(label);
            }

            // 写入数据
            workbook.write();
            workbook.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void poiExport() {
        String[] titles = {"id", "name", "sex"};
        // 创建Excel工作簿
        HSSFWorkbook workbook = new HSSFWorkbook();
        // 创建Sheet
        HSSFSheet sheet = workbook.createSheet();
        // 创建第一行
        HSSFRow row = sheet.createRow(0);

        for (int i=0; i<titles.length; i++) {
            HSSFCell cell = row.createCell(i);
            cell.setCellValue(titles[i]);
        }

        for (int i=1; i<10; i++) {
            HSSFRow nextRow = sheet.createRow(i);
            HSSFCell cell = nextRow.createCell(0);
            cell.setCellValue("a"+i);
            cell = nextRow.createCell(1);
            cell.setCellValue("user"+i);
            cell = nextRow.createCell(2);
            cell.setCellValue("male");
        }

        File file = new File("./poi_excel.xls");

        try {
            file.createNewFile();
            FileOutputStream stream = FileUtils.openOutputStream(file);
            workbook.write(stream);
            stream.close();
        } catch (IOException e) {
            e.printStackTrace();
        }

    }

    public static void main(String[] args) {
        //jxlExport();
        poiExport();
    }
}
